package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.bo.Customer;
import edu.byu.intex2.data.Cache;
import edu.byu.intex2.data.ConnectionPool;
import edu.byu.intex2.data.DataException;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import magis.CustomerSearch;

/**
 * Customer Data Access Object
 * @author Brady
 */
public class CustomerDAO {

    /////////////////////////////////////////////
    ///   Singleton code
    private static CustomerDAO instance = null;

    /** Creates a new instance of CustomerDAO */
    private CustomerDAO() {
    }//constructor

    /** Retrieves the single instance of this class */
    public static synchronized CustomerDAO getInstance() {
        if (instance == null) {
            instance = new CustomerDAO();
        }
        return instance;
    }//getInstance

    ////////////////////////////////////////////
    ///   CREATE methods
    /** Creates a new customer in the database  */
    public Customer create(String id) throws DataException {
        Customer customer = new Customer(id); // new Customer
        customer.setObjectAlreadyInDB(false); // not in DB
        Cache.getInstance().put(customer.getId(), customer); // put in cache
        return customer;
    }//create


    ////////////////////////////////////////////
    ///   READ methods
    /** Reads an existing customer from the database    */
    public Customer read(String id) throws DataException {
        // Get Object from Cache
        if (Cache.getInstance().containsKey(id)) {
            return (Customer) Cache.getInstance().get(id);
        }

        // Grab a database connection
        Connection conn = ConnectionPool.getInstance().get();

        try {
            return read(id, conn);
        } catch (Exception e) {
            throw new DataException("An error occurred while reading the customer object information.", e);
        } finally {
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to read an existing customer from the database    */
    synchronized Customer read(String id, Connection conn) throws Exception {
        // Check Cache
        if (Cache.getInstance().containsKey(id)) {
            return (Customer) Cache.getInstance().get(id);
        }

        // Prepare a SQL statement
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM customer where id=?");

        try {
            stmt.setString(1, id);
            ResultSet rs = stmt.executeQuery(); // result set
            if (rs.next()) {
                return readRecord(rs, conn);
            }//if
            throw new DataException("Customer with id '" + id + "' not found.");
        } finally {
            stmt.close();
        }
    }//read

    /** Internal method to create a customer object from a record   */
    synchronized Customer readRecord(ResultSet rs, Connection conn) throws Exception {
        if (Cache.getInstance().containsKey(rs.getString("id"))) {
            return (Customer) Cache.getInstance().get(rs.getString("id"));
        }
        // Create new customer
        Customer customer = new Customer(rs.getString("id"));
        customer.setObjectAlreadyInDB(true); // object is in DB

        // Put customer in cache
        Cache.getInstance().put(customer.getId(), customer);

        // Set Customer Attributes
        customer.setHighPrice(rs.getDouble("high_price"));
        customer.setLowPrice(rs.getDouble("low_price"));

        // Set Person Attributes
        PersonDAO.getInstance().setPersonValues(customer, conn);

        // Customer is clean
        customer.setDirty(false);

        return customer;
    }//readRecord

    /////////////////////////////////////////////
    ///   UPDATE methods
    /** Saves an existing customer in the database */
    public void save(Customer customer) throws DataException {
        // get a connection from the pool, save all of the sales first using the other person method and then commit them in the database
        Connection conn = ConnectionPool.getInstance().get();

        try {
            save(customer, conn); // save with connection
            conn.commit();  // commit the db transaction
        } catch (Exception e) {
            try {
                //rollback if there were any errors saving, catch any other errors you might have
                conn.rollback();
            } catch (SQLException e2) {
                throw new DataException("Could not roll back the person database transaction!", e2);
            }
            throw new DataException("An error occurred while saving the customer object information.", e);
        } finally {
            // release the connection
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to update a customer in the database */
    void save(Customer customer, Connection conn) throws Exception {
        // put the person object to cache
        Cache.getInstance().put(customer.getId(), customer);

        // decide whether to update or insert into the database
        if (customer.isObjectAlreadyInDB() && customer.isDirty()) {
            // Update Person in databsae
            PersonDAO.getInstance().saveNoCommit(customer, conn);

            // Update Customer
            update(customer, conn);
        } else if (customer.isDirty()) {
            // Insert Person to databsae
            PersonDAO.getInstance().saveNoCommit(customer, conn);

            // Insert customer into databsae
            insert(customer, conn);
        }//if
    }

    /** Saves an existing customer to the database */
    private void update(Customer customer, Connection conn) throws Exception {
        // use a prepared statement to prevent sql injection
        PreparedStatement stmt = conn.prepareStatement("UPDATE customer SET high_price=?, low_price=?  WHERE id=?");
        try {
            // Get Person Attributes and add them to the statement
            stmt.setDouble(1, customer.getHighPrice());
            stmt.setDouble(2, customer.getLowPrice());
            stmt.setString(3, customer.getId());
            stmt.execute();

            // Person is clean!
            customer.setDirty(false);
        } finally {
            stmt.close();  // make sure to close the prepared statement
        }
    }

    /** Inserts a new customer into the database */
    private void insert(Customer customer, Connection conn) throws Exception {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO customer (id, high_price, low_price) VALUES (?, ?, ?)");
        try {
            // Set Person Attributes
            stmt.setString(1, customer.getId());
            stmt.setDouble(2, customer.getHighPrice());
            stmt.setDouble(3, customer.getLowPrice());
            stmt.execute();

            customer.setObjectAlreadyInDB(true); // now in DB
            customer.setDirty(false); // object is clean
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            stmt.close(); // close the prepared statement
        }
    }

    /////////////////////////////////////////////////
    ///   DELETE methods
    /** We do not support deleting of business objects in this application */
    public void delete(Customer customer) throws DataException {
        throw new RuntimeException("Nice try. The delete function is not supported in this application.");
    }

    ////////////////////////////////////////////////
    ///   SEARCH methods
    /** Retrieves all customers in a list of arrays from the database */
    public List<CustomerSearch> getAll() throws Exception {
        //this method does not use the standard search method because we don't want to get EVERY customer object back from here, there could be 10's of
        //thousands in the future. instead, we want their full name and phone number so we can populate our search list. so our query should be designed
        //as such. this method will return a List of type String array.

        Connection conn = ConnectionPool.getInstance().get();
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM customer join person on customer.id = person.id");

        try {
            ResultSet rs = stmt.executeQuery();
            List<CustomerSearch> customers = new ArrayList<CustomerSearch>();

            // Create an array of CustomerSearch objects (simplified objects to display the date in the model
            while (rs.next()) {
                // Add cs to list
                customers.add(readCustomerSearchRecord(rs));
            }

            // Return list of customerSearch objects
            return customers;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            stmt.close();
            ConnectionPool.getInstance().release(conn);
        }

        return null;
    }

    /* Internal reading of a customerSearch resultSet */
    private CustomerSearch readCustomerSearchRecord(ResultSet rs) throws Exception {
        CustomerSearch cs = new CustomerSearch();
        cs.setId(rs.getString("id"));
        cs.setFirstName(rs.getString("first_name"));
        cs.setLastName(rs.getString("last_name"));
        cs.setPhone(rs.getString("phone"));

        return cs;
    }

    /** Internal method to search by certain fields */
    private List<CustomerSearch> search(PreparedStatement stmt, Connection conn) throws Exception {
        List<CustomerSearch> customers = new ArrayList<CustomerSearch>();

        try {
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                customers.add(readCustomerSearchRecord(rs));
            }
        } finally {
            stmt.close();
            ConnectionPool.getInstance().release(conn);
        }

        return customers;
    }

    /* Allow user to search by either first or last name or phone */
    public List<CustomerSearch> getByAnyName(String any) throws Exception {
        //establish connection and prepared statement 
        Connection conn = ConnectionPool.getInstance().get();
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM customer JOIN person ON person.id = customer.id WHERE person.FIRST_NAME LIKE '%" + any + "%' OR person.LAST_NAME LIKE '%" + any + "%' OR person.PHONE LIKE '%" + any + "%'");

        // run stmt through the search method, return a list of customers matching any name or phone number
        return search(stmt, conn);
    }

    public Customer getByName(String fName, String lName, String pNumber) throws DataException {
        //establish connection and prepared statement run it through the search method, return a list of customers matching the Name and phone number
        return null;
    }

    public Customer getByCustomerNumber(String custNum) throws DataException {
        //establish connection and prepared statement run it through the search method, return a Customer matching the customer number
        return null;
    }
}
